Imports System.Data.SqlClient
Imports System
Imports System.Configuration
Public Class DCargos

    Dim conn As New DConexion
    Public Function cargarCargos(ByVal item As Entidades.ECargo) As Boolean
        Dim com As New SqlCommand
        Dim band As Boolean
        Try
            conn.abrirConexion()
            com.Connection = conn.obtenerConexion
            com.CommandType = CommandType.StoredProcedure
            com.CommandText = "CargarCargo"
            'carga os parametros
            com.Parameters.AddWithValue("@cargo", item.Cargo)
            'executa el comando
            com.ExecuteNonQuery()
            band = True
        Catch ex As Exception
            Throw New ArgumentException(ex.Message)
        End Try
        'cierra conexion
        conn.cerrarConexion()
        Return band
    End Function

    Public Function modificarCargo(ByVal item As Entidades.ECargo) As Boolean
        Dim com As New SqlCommand
        Dim band As Boolean
        Try
            conn.abrirConexion()
            com.Connection = conn.obtenerConexion
            com.CommandType = CommandType.StoredProcedure
            com.CommandText = "ModificarCargo"
            'carga os parametros
            com.Parameters.AddWithValue("@codigo", item.Codigo)
            com.Parameters.AddWithValue("@cargo", item.Cargo)

            com.ExecuteNonQuery()
            band = True
        Catch ex As Exception
            Throw New ArgumentException(ex.Message)
        End Try
        conn.cerrarConexion()
        Return band
    End Function

    'SOBRECARGADO: busca cargos por descripcion
    Public Function buscarCargos(ByVal item As String) As List(Of Entidades.ECargo)
        Dim com As New SqlCommand
        Dim coleccion As New List(Of Entidades.ECargo)
        Try
            conn.abrirConexion()
            com.Connection = conn.obtenerConexion
            com.CommandType = CommandType.StoredProcedure
            com.CommandText = "BuscarCargo"
            'carga os parametros
            com.Parameters.AddWithValue("@cargo", item)
            Dim dr As SqlDataReader
            dr = com.ExecuteReader
            Do While dr.Read
                'creamos un objeto
                Dim objeto As New Entidades.ECargo
                objeto.Codigo = dr("codigo")
                objeto.Cargo = dr("cargo")
                coleccion.Add(objeto)
                objeto = Nothing
            Loop
            conn.cerrarConexion()
            'retornamos los valores encontrados
            Return coleccion
        Catch ex As Exception
            Throw New ArgumentException(ex.Message)
        Finally
            coleccion = Nothing
        End Try
    End Function

    'SOBRECARGADO: busca todos los cargos
    Public Function buscarCargos() As List(Of Entidades.ECargo)
        Dim com As New SqlCommand
        Dim coleccion As New List(Of Entidades.ECargo)
        Try
            conn.abrirConexion()
            com.Connection = conn.obtenerConexion
            com.CommandType = CommandType.StoredProcedure
            com.CommandText = "BuscarCargos"
            Dim dr As SqlDataReader
            dr = com.ExecuteReader
            Do While dr.Read
                'creamos un objeto
                Dim objeto As New Entidades.ECargo
                objeto.Codigo = dr("codigo")
                objeto.Cargo = dr("cargo")

                coleccion.Add(objeto)
                objeto = Nothing
            Loop
            conn.cerrarConexion()
            'retornamos los valores encontrados
            Return coleccion
        Catch ex As Exception
            Throw New ArgumentException(ex.Message)
        Finally
            coleccion = Nothing
        End Try
    End Function

    Public Function buscarCargoPorLegajo(ByVal codigo As Integer) As Entidades.ECargo
        Dim com As New SqlCommand
        Dim objeto As New Entidades.ECargo
        Try
            conn.abrirConexion()
            com.Connection = conn.obtenerConexion
            com.CommandType = CommandType.StoredProcedure
            com.CommandText = "BuscarCargoxCodigo"
            com.Parameters.AddWithValue("@codigo", codigo)
            Dim dr As SqlDataReader
            dr = com.ExecuteReader
            Do While dr.Read
                objeto.Codigo = dr("codigo")
                objeto.Cargo = dr("cargo")
            Loop
            Return objeto
        Catch ex As Exception
            Throw New ArgumentException(ex.Message)
        Finally
            objeto = Nothing
        End Try
    End Function

    Public Function traerProximoCodigo() As Integer
        conn.abrirConexion()
        Dim com As New SqlCommand
        com.Connection = conn.obtenerConexion
        com.CommandType = CommandType.StoredProcedure
        com.CommandText = "TraerProximoCodCargo"
        Dim proximo As Integer
        Try
            Dim dr As SqlDataReader
            dr = com.ExecuteReader
            Do While dr.Read
                proximo = dr("proxcodigo")
            Loop
            dr.Close()
            com.Dispose()
        Catch ex As Exception
            Throw New ArgumentException(ex.Message)
        End Try
        Return proximo
    End Function

    Public Function traerCodigoCargo(ByVal cargo As Entidades.ECargo) As Integer
        conn.abrirConexion()
        Dim com As New SqlCommand
        com.Connection = conn.obtenerConexion
        com.CommandType = CommandType.StoredProcedure
        com.CommandText = "TraerCodigoCargo"
        com.Parameters.AddWithValue("@nombre", cargo.Cargo)
        Dim codigo As New Integer
        Try
            Dim dr As SqlDataReader
            dr = com.ExecuteReader
            Do While dr.Read
                codigo = dr("codigo")
            Loop
            'retornamos los valores encontrados
            Return codigo
        Catch ex As Exception
            Throw New ArgumentException(ex.Message)
        Finally
        End Try
        conn.cerrarConexion()
    End Function
End Class
